package com.transcargo.db;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Properties;
import java.io.InputStream;
import java.io.FileInputStream;

import org.apache.log4j.Logger;

import javax.servlet.ServletException;

import com.transcargo.config.ConfigManager;

/**
 * Date: 13.01.2011
 * Time: 14:20:20
 */
public class db {
    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;
    private String error_st = "";
    private static final Logger LOG = Logger.getLogger(db.class);

    public void finalize() throws Throwable {
        if (conn != null) conn.close();
        super.finalize();
    }
    public class PreStatement {
        private PreparedStatement st = null;
        private int counter = 1;
        public PreStatement(Connection conn, String statement) throws java.sql.SQLException {
            st = conn.prepareStatement(statement);
            LOG.debug("Preparing query statement: " + statement);
        }
        public void Insert(String param) throws java.sql.SQLException {
            if (st != null) {
                st.setString(counter, param);
                counter++;
            }
        }
        public void Insert(int param) throws java.sql.SQLException {
            if (st != null) {
                st.setInt(counter, param);
                counter++;
            }
        }
        public void Insert(float param) throws java.sql.SQLException {
            if (st != null) {
                st.setFloat(counter, param);
                counter++;
            }
        }
        public ResultSet execute() throws java.sql.SQLException {
            if (st != null)
                return st.executeQuery();
            else return null;
        }
        public void executeUpdate() throws java.sql.SQLException {
            if (st != null)
                st.executeUpdate();
        }
    }

    public boolean init() throws ServletException {
        boolean res = false;
        try {
			//conn = DriverManager.getConnection("jdbc:mysql://localhost/" + dbname + "?" +
            //                               "user=" + username + "&password=" + dbpass);
            conn = DriverManager.getConnection(ConfigManager.getProperty("DB_URL"));
            res = true;
        } catch (SQLException ex) {
            // handle any errors
        	LOG.fatal("Unable to connect to DB!!!");
        	LOG.error("SQLException: " + ex.getMessage());
        	LOG.error("SQLState: " + ex.getSQLState());
        	LOG.error("VendorError: " + ex.getErrorCode());
        }
        return res;
    }
    public PreStatement CreateStatement(String statement) throws java.sql.SQLException
    {
        return new PreStatement(conn, statement);
    }
    public ArrayList<String[]> execute(String statement) throws dbException {
        ArrayList<String[]> result = null;
        if (conn == null) throw new dbException("Mysql driver is not connected");
        try {
            stmt = conn.createStatement();
            LOG.debug("Query statement: " + statement);
            rs = stmt.executeQuery(statement);
            ResultSetMetaData md = rs.getMetaData();
            int columns =  md.getColumnCount();
            if (columns > 0) {
                result = new ArrayList<String[]>();
            }
            while (rs.next()) {
                int i;
                String[] res = new String[columns];
                for (i = 1; i <= columns; i++) {

                    res[i-1] = rs.getString(i);
                }
                result.add(res);
            }

    } catch (SQLException ex){
        // handle any errors
    	LOG.error("SQLException: " + ex.getMessage());
    	LOG.error("SQLState: " + ex.getSQLState());
    	LOG.error("VendorError: " + ex.getErrorCode());
    }
    finally {
        // it is a good idea to release
        // resources in a finally{} block
        // in reverse-order of their creation
        // if they are no-longer needed
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) { } // ignore
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) { } // ignore
            stmt = null;
        }
    }
    return result;
    }
    public void executeUpdate(String statement) throws dbException {
        if (conn == null) throw new dbException("Mysql driver is not connected");
        try {
            LOG.debug("Query statement: " + statement);
            stmt = conn.createStatement();
            stmt.executeUpdate(statement);

    } catch (SQLException ex){
        // handle any errors
    	LOG.error("SQLException: " + ex.getMessage());
    	LOG.error("SQLState: " + ex.getSQLState());
    	LOG.error("VendorError: " + ex.getErrorCode());
    }
    }
    
    public ArrayList<String[]> execute(PreStatement statement) throws dbException {
        ArrayList<String[]> result = null;
        if (conn == null) throw new dbException("Mysql driver is not connected");
        try {
            rs = statement.execute();
            ResultSetMetaData md = rs.getMetaData();
            int columns =  md.getColumnCount();
            if (columns > 0) {
                result = new ArrayList<String[]>();
            }
            while (rs.next()) {
                int i;
                String[] res = new String[columns];
                for (i = 1; i <= columns; i++) {

                    res[i-1] = rs.getString(i);
                }
                result.add(res);
            }

    } catch (SQLException ex){
        // handle any errors
    	LOG.error("SQLException: " + ex.getMessage());
    	LOG.error("SQLState: " + ex.getSQLState());
    	LOG.error("VendorError: " + ex.getErrorCode());
        error_st = ex.getMessage();
    }
    finally {
        // it is a good idea to release
        // resources in a finally{} block
        // in reverse-order of their creation
        // if they are no-longer needed
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) { } // ignore
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) { } // ignore
            stmt = null;
        }
    }
    return result;
    }

    public void executeUpdate(PreStatement statement) throws dbException {
        if (conn == null) throw new dbException("Mysql driver is not connected");
        try {
            statement.executeUpdate();

        } catch (SQLException ex){
            // handle any errors
        	LOG.error("SQLException: " + ex.getMessage());
        	LOG.error("SQLState: " + ex.getSQLState());
        	LOG.error("VendorError: " + ex.getErrorCode());
            error_st = ex.getMessage();
        }
        finally {
        // it is a good idea to release
        // resources in a finally{} block
        // in reverse-order of their creation
        // if they are no-longer needed
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) { } // ignore
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) { } // ignore
            stmt = null;
        }
    }
    }

    public String getError() {
        return error_st;
    }
}
